home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
AltaVista Media Wrangler 2.0
/
AltaVista Media Wrangler 2.0.iso
/
mwrangle
/
database
/
infosch.sql
< prev
next >
Wrap
Text File
|
1995-02-28
|
7KB
|
180 lines
-- ;
-- SQL Script for creating Info_Schema Views ;
-- ;
-- For Internal Use only ;
-- ;
SET SCHEMA info_schema;
CREATE VIEW schemata (sys_catalog, sys_schema, sys_owner) AS
SELECT sys_catalog_name, sys_schema_name, sys_user_or_group_name
FROM defn_schema.sys_schema, defn_schema.sys_user
WHERE (sys_user_id = sys_schema_owner_id);
GRANT SELECT ON info_schema.schemata TO PUBLIC;
CREATE VIEW domains (sys_domain, sys_datatype, sys_length,
sys_numeric_scale) AS
SELECT sys_domain_name, sys_data_type, sys_max_length,
sys_numeric_scale
FROM defn_schema.sys_domains, defn_schema.sys_datatype
WHERE (sys_domain_id = sys_table_or_domain_id);
GRANT SELECT on info_schema.domains to public;
CREATE view all_tables (sys_schema,sys_tablename, sys_tabletype,
sys_version, sys_filename) AS
SELECT sys_schema_name, sys_table_name, sys_table_type,
sys_version_number, sys_file_name
FROM defn_schema.sys_schema, defn_schema.sys_tables
WHERE sys_tables.sys_schema_id = sys_schema.sys_schema_id;
GRANT SELECT on info_schema.all_tables to public;
CREATE view views (sys_schema, sys_viewname,sys_viewdef,
sys_check_option, sys_updateflag, sys_groupview_flag) AS
SELECT sys_schema_name, sys_table_name, sys_view_definition,
sys_check_option, sys_updatable_flag, sys_group_view_flag
FROM defn_schema.sys_schema, defn_schema.sys_tables,
defn_schema.sys_views
WHERE ((sys_schema.sys_schema_id = sys_tables.sys_schema_id AND
sys_table_id = sys_view_id) AND (
(sys_view_id) in
(SELECT sys_view_id
FROM defn_schema.sys_view_and_table_column_usag
)
)
);
GRANT SELECT on info_schema.views to public;
CREATE view columns_and_tables (sys_schema, sys_tablename,
sys_colname, sys_datatype, sys_column_position,
sys_nullable_flag) AS
SELECT sys_schema_name, sys_table_name, sys_column_name,
sys_data_type, c.sys_column_position,
sys_nullable_flag
FROM defn_schema.sys_schema AS a,
defn_schema.sys_tables AS b,
defn_schema.sys_columns AS c,
defn_schema.sys_datatype AS d
WHERE (a.sys_schema_id = b.sys_schema_id AND
b.sys_table_id = c.sys_table_id AND
b.sys_table_id = d.sys_table_or_domain_id AND
c.sys_column_position = d.sys_column_position);
GRANT SELECT on info_schema.columns_and_tables to public;
CREATE view table_privileges (sys_schema, sys_tablename,
sys_privilege) AS
SELECT sys_schema_name, sys_table_name, sys_privilege
FROM defn_schema.sys_schema, defn_schema.sys_table_column_priv,
defn_schema.sys_tables
WHERE (sys_tables.sys_table_id = sys_table_column_priv.sys_table_id
AND sys_schema.sys_schema_id = sys_tables.sys_schema_id);
GRANT SELECT on info_schema.table_privileges to public;
CREATE view column_privileges (sys_schema, sys_tablename,
sys_column_name, sys_privilege) AS
SELECT sys_schema_name, sys_table_name, sys_column_name,
sys_privilege
FROM defn_schema.sys_schema, defn_schema.sys_table_column_priv,
defn_schema.sys_tables, defn_schema.sys_columns
WHERE (sys_tables.sys_schema_id = sys_schema.sys_schema_id AND
sys_columns.sys_table_id = sys_tables.sys_table_id AND
sys_columns.sys_table_id = sys_table_column_priv.sys_table_id);
GRANT SELECT on info_schema.column_privileges to public;
CREATE view table_constraints (sys_schema, sys_tablename, sys_constraint,
sys_keytype, sys_constraint_type, sys_deferrable,
sys_initdeferred, sys_indexcreated_flag) AS
SELECT sys_schema_name, sys_table_name, sys_constraint_name,
sys_key_type, sys_constraint_type, sys_deferrable,
sys_initially_deferred, sys_index_created_flag
FROM defn_schema.sys_schema, defn_schema.sys_tables,
defn_schema.sys_table_column_constraint
WHERE (sys_tables.sys_schema_id = sys_schema.sys_schema_id AND
sys_tables.sys_table_id =
sys_table_column_constraint.sys_table_id);
GRANT SELECT on info_schema.table_constraints to public;
CREATE view ref_constraints (sys_schema, sys_tablename, sys_constraint,
sys_match_constraint, sys_match_option, sys_update_rule,
sys_delete_rule) AS
SELECT sys_schema_name, sys_table_name, a.sys_constraint_name,
b.sys_constraint_name, sys_match_option, sys_update_rule,
sys_delete_rule
FROM defn_schema.sys_schema, defn_schema.sys_tables,
defn_schema.sys_referential_constraint AS c,
defn_schema.sys_table_column_constraint AS a,
defn_schema.sys_table_column_constraint AS b
WHERE ( sys_schema.sys_schema_id = sys_tables.sys_schema_id AND
c.sys_constraint_id = a.sys_constraint_id AND
c.sys_match_constraint_id = b.sys_constraint_id);
GRANT SELECT on info_schema.ref_constraints to public;
CREATE view check_constraints (sys_schema, sys_tablename, sys_constraint,
sys_match_constraint, sys_check_detail) AS
SELECT sys_schema_name, sys_table_name, a.sys_constraint_name,
b.sys_constraint_name, sys_check_detail
FROM defn_schema.sys_schema, defn_schema.sys_tables,
defn_schema.sys_check_constraint,
defn_schema.sys_table_column_constraint AS a,
defn_schema.sys_table_column_constraint AS b,
defn_schema.sys_referential_constraint AS c
WHERE (sys_schema.sys_schema_id = sys_tables.sys_schema_id AND
c.sys_constraint_id = a.sys_constraint_id AND
c.sys_match_constraint_id = b.sys_constraint_id AND
sys_check_constraint.sys_constraint_id = a.sys_constraint_id);
GRANT SELECT on info_schema.check_constraints to public;
CREATE view key_column_usage (sys_schema, sys_tablename, sys_constraint,
sys_column_name, sys_colpos, sys_key_colpos) AS
SELECT sys_schema_name, sys_table_name, sys_constraint_name,
sys_column_name, c.sys_column_position,
sys_key_column_position
FROM defn_schema.sys_schema AS s, defn_schema.sys_tables AS t,
defn_schema.sys_columns AS c,
defn_schema.sys_table_column_constraint AS tc,
defn_schema.sys_key_column_usage AS kcu
WHERE (s.sys_schema_id = t.sys_schema_id AND
t.sys_table_id = c.sys_table_id AND
tc.sys_constraint_id = kcu.sys_constraint_id AND
tc.sys_table_id = t.sys_table_id);
GRANT SELECT on info_schema.key_column_usage to public;
CREATE view users (sys_username, sys_default_catalog, sys_default_schema,
sys_login_status) AS
SELECT sys_user_or_group_name, sys_default_catalog,
sys_default_schema, sys_current_login_status
FROM defn_schema.sys_user;
GRANT SELECT on info_schema.users to public;
CREATE view catalogs (sys_catalog, sys_path, sys_journal_file,
sys_secure_mode) AS
SELECT sys_catalog_name, sys_path_name, sys_journal_file_name,
sys_secure_mode
FROM defn_schema.sys_catalog;
GRANT SELECT on info_schema.catalogs to public;
CREATE view tables_in_current_schema (sys_schema, sys_tablename,
sys_tabletype, sys_version, sys_file_name) AS
SELECT sys_schema_name, sys_table_name, sys_table_type,
sys_version_number, sys_file_name
FROM defn_schema.sys_tables AS a, defn_schema.sys_schema AS b
WHERE (a.sys_schema_id = b.sys_schema_id) AND
(upper(b.sys_schema_name) = upper (current_schema));
GRANT SELECT on info_schema.tables_in_current_schema to public;
-- End Of The Script --- ;